import datetime
import sqlite3
import time
import matplotlib.dates as mpd
import matplotlib as mpl
import matplotlib.dates as mdates
import matplotlib.patches as patches
import matplotlib.pyplot as plt
import numpy as np
from matplotlib import dates as mdates
from matplotlib import ticker

from mpl_finance import candlestick2_ohlc, plot_day_summary_oclh

plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

DB_PATH = './db/fxdata.db'
# 绘图数据对象
class candle_list_Obj(object):
    def __init__(self, timestamp_list, dateStr_list, open_list, high_list, low_list, close_list):
        self.timestamp_list = timestamp_list
        self.dateStr_list = dateStr_list
        self.open_list = open_list
        self.high_list = high_list
        self.low_list = low_list
        self.close_list = close_list

    # 价格对象


class priceObj(object):
    def __init__(self, time, dateStr, opens, high, low, close):
        self.time = time
        self.dateStr = dateStr
        self.opens = opens
        self.high = high
        self.low = low
        self.close = close

    # 财经对象


class eventObj(object):
    def __init__(self, timestamp, dateStr, country, eventName, beforeValue, nowValue, expectValue):
        self.timestamp = timestamp  # 新闻发布时间
        self.dateStr = dateStr  # 日期字符串
        self.country = country  # 国家
        self.eventName = eventName  # 新闻名字
        self.nowValue = nowValue  # 实际值
        self.expectValue = expectValue  # 预测值
        self.beforeValue = beforeValue  # 前值








def timestamp2str(timestamp):
    #转换成localtime
    time_local = time.localtime(timestamp)
    #转换成新的时间格式(2016-05-05 20:28:54)
    dt = time.strftime("%Y-%m-%d %H:%M:%S",time_local)
    return dt

def getZerotimestamp(timestamp):
    '''获取时间戳当日零点的时间戳
    
    [description]
    
    Arguments:
        timestamp {[type]} -- [description]
    
    Returns:
        [type] -- [description]
    '''
    dt = datetime.datetime.fromtimestamp(timestamp)
    dtime = datetime.datetime(dt.year, dt.month, dt.day, 0, 0, 0)
    _ts = time.mktime(dtime.timetuple())
    return int(_ts)

def getNextDayZero_timestamp(timestamp,daynum):
    '''获取时间戳下n天零点时间戳
    
    [description]
    
    Arguments:
        timestamp {[type]} -- [description]
    
    Returns:
        [type] -- [description]
    '''
    dt = datetime.datetime.fromtimestamp(timestamp)
    dtime = datetime.datetime(dt.year, dt.month, dt.day, 0, 0, 0)
    _ts = time.mktime(dtime.timetuple())
    next_day_timestamp = int(_ts) + 86400*daynum
    return next_day_timestamp

def get_timestamp_space(timestamp1,timestamp2):
    '''返回两个时间戳(秒)相差的绝对值
    
    [description]
    
    Arguments:
        timestamp1 {[type]} -- [description]
        timestamp2 {[type]} -- [description]
    
    Returns:
        [type] -- [description]
    '''
    d1 = datetime.datetime.fromtimestamp(timestamp1)
    d2 = datetime.datetime.fromtimestamp(timestamp2)
    s = d1 - d2
    return abs(s.days)
def getdata_by_sql(sqlsrt):
    '''从sqlite3 中获取数据 ,where子句可填充,之前的不可以
    
    [description]
    
    Arguments:
        sqlsrt {[type]} -- [description]
        num {[type]} -- [description]
    
    Returns:
        [type] -- [description]
    '''

    datetime_list = []
    timestamp_list = []
    open_list = []
    high_list = []
    low_list = []
    close_list = []
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    sql = 'SELECT datetime,open,high,low,close,timestamp  from eurusd_15 '+sqlsrt
    print("sql:",sql)
    cursor = c.execute(sql)
    for row in cursor:
        datestr = row[0]
        opens = row[1]
        high = row[2]
        low = row[3]
        close = row[4]
        timestamp = row[5]

        datetime_list.append(datestr)
        timestamp_list.append(timestamp)
        open_list.append(opens)
        high_list.append(high)
        low_list.append(low)
        close_list.append(close)

    conn.close()
    cObj = candle_list_Obj(timestamp_list, datetime_list, open_list, high_list, low_list, close_list)
    return cObj



def getdata_by_timestamp(start, end):
    '''从sqlite3 中获取数据

    [description]

    Arguments:
        start {[type]} -- [description]
        end {[type]} -- [description]

    Returns:
        xdata [list] -- [时间的坐标轴]
    '''

    datetime_list = []
    timestamp_list = []
    open_list = []
    high_list = []
    low_list = []
    close_list = []
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    sql = 'SELECT datetime,open,high,low,close,timestamp  from eurusd_15 where timestamp >= %i and timestamp <= %i' % (start, end)
    print("sql:",sql)
    cursor = c.execute(sql)
    for row in cursor:
        datestr = row[0]
        opens = row[1]
        high = row[2]
        low = row[3]
        close = row[4]
        timestamp = row[5]

        datetime_list.append(datestr)
        timestamp_list.append(timestamp)
        open_list.append(opens)
        high_list.append(high)
        low_list.append(low)
        close_list.append(close)

    conn.close()
    cObj = candle_list_Obj(timestamp_list, datetime_list, open_list, high_list, low_list, close_list)
    return cObj


# '%Y-%m-%d'
def str_to_datetime(t_str,date_format):
    d = datetime.datetime.strptime(t_str, date_format)
    return d

def dateStr2timestamp(t_str,date_format):
    d = datetime.datetime.strptime(t_str, date_format)
    return int(time.mktime(d.timetuple()))



# 绘制基础K线图
def draw_candlestick_base2(fig, ax,cObj):
    datetime_list = cObj.dateStr_list
    # print(type(datetime_list[0]))
    timestamp_list = cObj.timestamp_list
    open_list = cObj.open_list
    high_list = cObj.high_list
    low_list = cObj.low_list
    close_list = cObj.close_list

    candlestick2_ohlc(ax, open_list, high_list, low_list, close_list, width=0.6, colorup='red', colordown='black')

    # 绘制垂线 按照天为分割线
    # 找出所有天数的分割线索引
    vline_index = []
    # 最后遍历的时间戳
    last_ts = 0
    for index, xd in enumerate(timestamp_list):
        # 相隔天数大于1 或者 本身这跟是00:00就加入划线集合
        conditionA = False
        # ds = get_timestamp_space(last_ts,xd)
        # print(ds)
        if last_ts != 0 and get_timestamp_space(last_ts,xd) > 1:
            conditionA = True
            
        conditionB = False
        dt = datetime.datetime.fromtimestamp(xd)
        if dt.hour == 0 and dt.second == 0 and dt.minute == 0:
            conditionB = True

        if conditionA or conditionB:
            vline_index.append(index)

        last_ts = xd



    minx = min(low_list)
    for v in vline_index:
        plt.axvline(x=v,alpha=0.5)
        bbox_props = dict(boxstyle="round", fc="w", ec="0.5", alpha=0.9)
        ax.text(v, minx, datetime_list[v], ha="center", va="bottom", size=10, bbox=bbox_props)
    # 绘制横线
    # plt.axhline(y=1.094)
    # plt.savefig("test.png", bbox_inches='tight')
    fig.tight_layout(pad=0)
    # 调整边距
    # plt.subplots_adjust(left=0.1)
    ax.xaxis.set_major_locator(ticker.MaxNLocator(2))  # 显示坐标轴的个数

    def mydate(x, pos):
        try:
            return datetime_list[int(x)]
        except IndexError:
            return ''

    ax.xaxis.set_major_formatter(ticker.FuncFormatter(mydate))





# 绘制基础K线图
def draw_candlestick_base(cObj):
    datetime_list = cObj.dateStr_list
    # print(type(datetime_list[0]))
    timestamp_list = cObj.timestamp_list
    open_list = cObj.open_list
    high_list = cObj.high_list
    low_list = cObj.low_list
    close_list = cObj.close_list

    fig, ax = plt.subplots(figsize=(15, 9))
    candlestick2_ohlc(ax, open_list, high_list, low_list, close_list, width=0.6, colorup='red', colordown='black')

    # 绘制垂线 按照天为分割线
    # 找出所有天数的分割线索引
    vline_index = []
    # 最后遍历的时间戳
    last_ts = 0
    for index, xd in enumerate(timestamp_list):
        # 相隔天数大于1 或者 本身这跟是00:00就加入划线集合
        conditionA = False
        # ds = get_timestamp_space(last_ts,xd)
        # print(ds)
        if last_ts != 0 and get_timestamp_space(last_ts,xd) > 1:
            conditionA = True
            
        conditionB = False
        dt = datetime.datetime.fromtimestamp(xd)
        if dt.hour == 0 and dt.second == 0 and dt.minute == 0:
            conditionB = True

        if conditionA or conditionB:
            vline_index.append(index)

        last_ts = xd



    minx = min(low_list)
    for v in vline_index:
        plt.axvline(x=v,alpha=0.5)
        bbox_props = dict(boxstyle="round", fc="w", ec="0.5", alpha=0.9)
        ax.text(v, minx, datetime_list[v], ha="center", va="bottom", size=10, bbox=bbox_props)
    # 绘制横线
    # plt.axhline(y=1.094)
    # plt.savefig("test.png", bbox_inches='tight')
    fig.tight_layout(pad=0)
    # 调整边距
    # plt.subplots_adjust(left=0.1)
    ax.xaxis.set_major_locator(ticker.MaxNLocator(2))  # 显示坐标轴的个数

    def mydate(x, pos):
        try:
            return datetime_list[int(x)]
        except IndexError:
            return ''

    ax.xaxis.set_major_formatter(ticker.FuncFormatter(mydate))

    return plt, fig, ax



def get_eventlist_byName(counrty,namestr):
    '''依据事件名称查询
    
    [description]
    
    Arguments:
        namestr {[type]} -- [description]
    
    Returns:
        [type] -- [description]
    '''
    # 模糊查询
    # args = '%'+namestr+'%'
    # sqlstr = "where country == '%s' and eventName  like '%s'" % (counrty,args)

    # 精确查询
    sqlstr = "where country == '%s' and eventName  = '%s'" % (counrty,namestr)
    return get_eventlist_bySql(sqlstr)



def get_eventlist_bySql(sqlstr):
    '''获取数据

    [description]

    Arguments:
        start {[type]} -- [description]
        end {[type]} -- [description]

    Returns:
        event_list [list] -- [返回事件的开始时间集合]
    '''
    data = []
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    sql = 'SELECT timestamp,Time,eventName,beforeValue,nowValue,expectValue,country  from event ' + sqlstr
    print("sql:",sql)
    cursor = c.execute(sql)
    last_time = 0
    last_event_name = ""
    for row in cursor:
        # 这里需要扣除8小时时差
        timestamp = int(row[0]) - 3600*8
        dateStr = row[1]
        eventName = row[2]
        beforeValue = row[3]
        nowValue = row[4]
        expectValue = row[5]
        country = row[6]

        # 判断重复
        repeat = set(last_event_name) & set(eventName)
        if len(repeat) >= 3 and last_time == timestamp:
            continue
        d = eventObj(timestamp, dateStr, country, eventName, beforeValue, nowValue, expectValue)

        data.append(d)

        last_time = timestamp
        last_event_name = eventName
    conn.close()
    return data





def get_eventlist_bytimestamp(start, end):
    '''获取数据

    [description]

    Arguments:
        start {[type]} -- [description]
        end {[type]} -- [description]

    Returns:
        event_list [list] -- [返回事件的开始时间集合]
    '''
    data = []
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    cursor = c.execute(
        'SELECT timestamp,Time,eventName,beforeValue,nowValue,expectValue,country  from event where timestamp >= %i and timestamp <= %i' % (
        start, end))
    last_time = 0
    last_event_name = ""
    for row in cursor:

        timestamp = int(row[0])
        dateStr = row[1]
        eventName = row[2]
        beforeValue = row[3]
        nowValue = row[4]
        expectValue = row[5]
        country = row[6]

        # 判断重复
        repeat = set(last_event_name) & set(eventName)
        if len(repeat) >= 3 and last_time == timestamp:
            continue
        d = eventObj(timestamp, dateStr, country, eventName, beforeValue, nowValue, expectValue)

        data.append(d)

        last_time = timestamp
        last_event_name = eventName
    conn.close()
    return data




def draw_event(plt, fig, ax, candleObj, event_list):
    timestamp_list = candleObj.timestamp_list
    high_list = candleObj.high_list
    low_list = candleObj.low_list
    # 绘制文字的坐标索引，如果重复，那个文字前面就加一个换行符
    text_index_map = {}
    for event in event_list:

        # 在价格数据中找出事件波动的范围
        start_timestamp = event.timestamp
        end_timestamp = start_timestamp + 3600

        event_name = event.eventName

        ########### 绘制基本面事件波动范围
        high_index = 0
        low_index = 0
        high = 0
        low = 999
        high_timestamp = 0
        low_timestamp = 0
        for i, ts in enumerate(timestamp_list):
            if ts < start_timestamp or ts > end_timestamp:
                continue
            if high_list[i] > high:
                high = high_list[i]
                high_index = i
                high_timestamp = ts
            if low_list[i] < low:
                low = low_list[i]
                low_index = i
                low_timestamp = ts

        size = low - high
        # 从结束时间往前画
        width = -1 * (end_timestamp - start_timestamp) / (timestamp_list[1] - timestamp_list[0])

        # 判断往前画还是往后画
        mark = 1

        end_index = 0
        if end_timestamp in timestamp_list:
            end_index = timestamp_list.index(end_timestamp)
        else:
            for i, ts in enumerate(timestamp_list):
                if end_timestamp > ts:
                    end_index = i
                    break
        if end_index == 0:
            print('在价格图表中没有找到对应价格 event= %s,end_timestamp= %s dateStr=%s' % (event_name, end_timestamp,timestamp2str(end_timestamp)))
            continue
        # 默认后到达最高
        end_point = high
        # 如果先到达最高
        if high_timestamp > low_timestamp:
            end_point = low
            mark = -1

        rectangle = patches.Rectangle(
            (end_index, end_point),  # (x,y)
            width,  # width 往后几根
            size * mark,  # 高度
            color="#9a9797",
            alpha=0.5  # 透明度
        )
        # print(width*mark,size)
        # print(high)
        # 在最低点下面标注文字

        # 绘制矩形
        ax.add_patch(rectangle)
        # 绘制文字

        if low_index in text_index_map:
            text_index_map[low_index] = text_index_map[low_index] - 0.001
        else:
            text_index_map[low_index] = low - 0.001
        ax.text(low_index, text_index_map[low_index], event_name)
        fig.autofmt_xdate()
        fig.tight_layout()
        plt.xticks(rotation=0)  # X轴坐标旋转度数





def draw_rectangle2(ax,quotes,start_d2n,end_d2n):
    #从开始往后画
    high_d2n = 0
    low_d2n = 0
    rectangle_high = 0
    rectangle_low = 999

    l = []
    for q in quotes:
        d2n = q[0]
        if d2n < start_d2n and d2n > end_d2n:
            continue
        high = q[3]
        low = q[4]
        l.append(low)
        if high > rectangle_high:
            rectangle_high = high
            high_d2n = d2n
        if rectangle_low > low:
            rectangle_low = low 
            low_d2n = d2n
    print("min",min(l))
    print(rectangle_high,rectangle_low)
    "默认先从低点往上画"
    mark = 1
    "先达到最高就是往下画"
    if high_d2n < low_d2n:
        mark = -1
    rectangle = patches.Rectangle(
        (start_d2n, rectangle_low),  # (x,y)
        end_d2n - start_d2n,  # width 往后几根
        mark*(rectangle_high-rectangle_low),  # 高度
        color="#9a9797",
        alpha=0.5  # 透明度
    )

    # 绘制矩形
    ax.add_patch(rectangle)


def draw_rectangle(fig, ax, candleObj, start_timestamp,end_timestamp, text):
    timestamp_list = candleObj.timestamp_list
    high_list = candleObj.high_list
    low_list = candleObj.low_list
    ########### 绘制基本面事件波动范围
    high_index = 0
    low_index = 0
    high = 0
    low = 999
    high_timestamp = 0
    low_timestamp = 0
    for i, ts in enumerate(timestamp_list):
        if ts < start_timestamp or ts > end_timestamp:
            continue
        if high_list[i] > high:
            high = high_list[i]
            high_index = i
            high_timestamp = ts
        if low_list[i] < low:
            low = low_list[i]
            low_index = i
            low_timestamp = ts

    size = low - high

    # 从结束时间往前画
    mark = 1

    end_index = 0
    if end_timestamp in timestamp_list:
        end_index = timestamp_list.index(end_timestamp)
    else:
        for i, ts in enumerate(timestamp_list):
            if end_timestamp < ts:
                end_index = i
                break
    if end_index == 0:
        print('在价格图表中没有找到对应价格 start=%s end=%s %s %s' % (start_timestamp, end_timestamp, timestamp2str(start_timestamp),timestamp2str(end_timestamp)))
        return 
    # 默认后到达最高
    end_point = high
    # 如果先到达最高
    if high_timestamp > low_timestamp:
        end_point = low
        mark = -1

    # 如果最开始的时间没有在列表中，那就取最小
    start_index = len(timestamp_list)
    if start_timestamp in timestamp_list:
        start_index = timestamp_list.index(start_timestamp)
    else:
        for i,ts in enumerate(timestamp_list):
            if start_timestamp < ts:
                start_index = i-1
                break
    width = -1 * (end_index - start_index)
    print(start_index,end_index)
    rectangle = patches.Rectangle(
        (end_index, end_point),  # (x,y)
        width,  # width 往后几根
        size * mark,  # 高度
        color="#9a9797",
        alpha=0.5  # 透明度
    )

    # 绘制矩形
    ax.add_patch(rectangle)
    # 绘制文字
    ax.text(start_index,high,text)
    return high,low


def moving_average(x, n, type='simple'):
    x = np.asarray(x)
    if type == 'simple':
        weights = np.ones(n)
    else:
        weights = np.exp(np.linspace(-1., 0., n))

    weights /= weights.sum()

    a = np.convolve(x, weights, mode='full')[:len(x)]
    a[:n] = a[n]
    return a



def getdata_to_USALine(table,start_timestamp,end_timestamp):
    """返回matplotlib绘制美国线的专用数据
    
    Arguments:
        table {[type]} -- [description]
        start_timestamp {[type]} -- [description]
        end_timestamp {[type]} -- [description]
    """

    quotes = []
    datestr_list = []
    conn = sqlite3.connect(DB_PATH)
    sql = 'SELECT datetime,open,high,low,close,timestamp  from %s where timestamp >= %i and timestamp <= %i' % (table,start_timestamp, end_timestamp)
    print("sql:",sql)
    cursor = conn.execute(sql)
    for row in cursor:
        datestr = row[0]
        opens = row[1]
        high = row[2]
        low = row[3]
        close = row[4]
        timestamp = row[5]

        d = datetime.datetime.fromtimestamp(timestamp)
        d2n = mpd.date2num(d)
        # 如果open = close = high = low,说明没有价格波动就不录入
        # if high == low:
        #     continue
        tp= (d2n,opens,close,high,low)
        print(datestr)
        quotes.append(tp)
        datestr_list.append(datestr)
    conn.close()
    return quotes


def get_data(table,whereSql):
    quotes = []
    conn = sqlite3.connect(DB_PATH)
    sql = 'SELECT datetime,open,high,low,close,timestamp  from %s %s' % (table,whereSql)
    print("sql:",sql)
    cursor = conn.execute(sql)
    for row in cursor:
        datestr = row[0]
        opens = row[1]
        high = row[2]
        low = row[3]
        close = row[4]
        timestamp = row[5]

        tp= priceObj(timestamp, datestr, opens, high, low, close)
        quotes.append(tp)
    conn.close()
    return quotes


def fin_max_density_line(candle_list_Obj):
    """
    寻找一个价格线被穿过的次数最多的价格
    """
    hlist = candle_list_Obj.high_list
    llist = candle_list_Obj.low_list

    priceMap = {}
    lens = len(hlist)
    nowPrice = min(llist)
    maxPrice = max(hlist)
    while nowPrice < maxPrice:
        for index in range(0,lens):
             if nowPrice <= hlist[index] and nowPrice >= llist[index]:
                 if nowPrice in priceMap:
                     priceMap[nowPrice] += 1 
                 else:
                     priceMap[nowPrice] = 1
        nowPrice += 0.00001
    # 依据map的value排序
    result_list = sorted(priceMap.items(), key=lambda d: d[1])
    print(result_list[-1][0])
    return result_list[-1][0]

def find_density_list(candle_list_Obj):
    """
    寻找一个价格线被穿过的次数最多的价格
    """
    hlist = candle_list_Obj.high_list
    llist = candle_list_Obj.low_list

    priceMap = {}
    lens = len(hlist)
    nowPrice = min(llist)
    maxPrice = max(hlist)
    while nowPrice < maxPrice:
        for index in range(0,lens):
             if nowPrice <= hlist[index] and nowPrice >= llist[index]:
                 if nowPrice in priceMap:
                     priceMap[nowPrice] += 1
                 else:
                     priceMap[nowPrice] = 1
        nowPrice += 0.00001
    # 依据map的value排序
    result_list = sorted(priceMap.items(), key=lambda d: d[1])
    r = []
    for i in result_list:
        r.append(i[0])
    return r